#include "excel.h"
#include "ui_excel.h"

#include <QMessageBox>
#include <QSqlError>
#include <QSqlRecord>
#include<QFileDialog>

#include <QDir>
#include<QFile>

Excel::Excel(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Excel)
{
    //    InitExcel();
    ui->setupUi(this);
    connectAllSignals();
}

Excel::~Excel()
{
    delete ui;
}

void Excel::InitExcel()
{
    myExcel = new QAxObject();
    if (myExcel->setControl("Excel.Application"))
    {
        qDebug() << "加载 Microsoft Excel 控件";
    }
    else
    {
        myExcel->setControl("ket.Application");  // 加载 WPS Excel 控件
    }
    myExcel->setProperty("Visible", false);
    myExcel->setProperty("DisplayAlerts", false);
}

void Excel::showTableString(const QStringList table)
{
    if(table.isEmpty())
        return;
    QTableWidget*tableWidget=ui->tableWidget;
    QStringList header=table.at(0).simplified().split(' ',QString::SkipEmptyParts);
    tableWidget->setRowCount(table.count()-1);
    tableWidget->setColumnCount(header.count());
    tableWidget->setHorizontalHeaderLabels(header);

    for(int i=1;i<table.count();i++)
    {
        QStringList line=table.at(i).split('\t',QString::KeepEmptyParts);
        for(int j=0;j<header.count();j++)
        {
            QTableWidgetItem*item=new QTableWidgetItem;
            item->setText(line.at(j));
            item->setTextAlignment(Qt::AlignCenter);
            tableWidget->setItem(i-1,j,item);
        }
    }
}

void Excel::connectAllSignals()
{
    connect(ui->openE,&QPushButton::clicked,this,&Excel::OpenExcel);
    connect(ui->saveE,&QPushButton::clicked,this,&Excel::SaveExcel);
    connect(ui->openSQL,&QPushButton::clicked,this,&Excel::OpenSQL);
    connect(ui->toSQL,&QPushButton::clicked,this,&Excel::ToSQL);
}

void Excel::LinkSQL()
{
    QString host;
    QString  user;
    QString password;
    QString database;
    int port;
    QString desktopPath = "./data.txt";
    QFile file(desktopPath);
    if (file.open(QIODevice::ReadOnly | QIODevice::Text))
    {
        QTextStream in(&file);
        QString line = in.readLine();
        QStringList data = line.split("\t");
        if (data.size()>= 2)
        {
            host=data[0];
            user=data[1];
            password=data[2];
            database=data[3];
            port=data[4].toInt();
        }
        file.close();
    }
    db = QSqlDatabase::addDatabase("QMYSQL");//添加mysql
    db.setHostName(host);
    db.setUserName(user);
    db.setPassword(password);
    db.setDatabaseName(database);
    db.setPort(port);
    if(!db.open())
    {
        qDebug() << db.lastError() <<endl;
        return ;
    }
}

void Excel::OpenExcel()
{
    //获取要读取得文件名
    QString fileName=QFileDialog::getOpenFileName(this,"读取","*.xlsx");
    if(fileName.isEmpty())
        return;
    fileName=QDir::toNativeSeparators(fileName);
    InitExcel();
    QAxObject*workBooks=myExcel->querySubObject("WorkBooks");
    qDebug()<<"加载 Microsoft Excel 控件"<<endl;
    workBooks->dynamicCall("Open(const QString&)",fileName);
    QAxObject*workBook=myExcel->querySubObject("ActiveWorkBook");
    QAxObject*mySheets=workBook->querySubObject("Sheets");
    QAxObject*sheet=mySheets->querySubObject("Item(int)",1);

    //获取已经使用的单元格区域，并得到行列数
    QAxObject*range=sheet->querySubObject("UsedRange");
    QAxObject*rows=range->querySubObject("Rows");
    QAxObject*colums=range->querySubObject("Columns");
    int count_row=rows->dynamicCall("Count").toInt();
    int count_col=colums->dynamicCall("Count").toInt();

    //提取单元格中内容，存放到StringList中
    QStringList tableString;
    QAxObject*cell;
    tableString.clear();
    for(int i=1;i<=count_row;i++)
    {
        QString line;
        for(int j=1;j<=count_col;j++)
        {
            cell=range->querySubObject("Cells(int,int)",i,j);
            line+=cell->dynamicCall("Value").toString()+"\t";
        }
        tableString.append(line);
    }

    //关闭工作簿、结束进程
    workBook->dynamicCall("Close()");
    myExcel->dynamicCall("Quit()");

    //显示得到的数据
    showTableString(tableString);
}

void Excel::SaveExcel()
{
    //获取要保存的文件名
    QString fileName=QFileDialog::getSaveFileName(this,"保存","*.xlsx");
    if(fileName.isEmpty())
        return;
    fileName=QDir::toNativeSeparators(fileName);

    //获取表格行列数
    QTableWidget*table=ui->tableWidget;
    int rows=table->rowCount();
    int columns=table->columnCount();

    //获取表头
    QStringList header;
    for(int i=0;i<columns;i++)
        header.append(table->horizontalHeaderItem(i)->text());

    //打开Excel进程，获取工作簿集合
    QAxObject*myExcell=new QAxObject("Excel.application",this);
    myExcell->setProperty("DisplayAlerts",false);
    QAxObject*workbooks=myExcell->querySubObject("WorkBooks");
    workbooks->dynamicCall("Add");

    //获取当前工作簿、工作表集合
    QAxObject*workbook=myExcell->querySubObject("ActiveWorkBook");
    QAxObject*sheets=workbook->querySubObject("Sheets");

    //获取当前工作表
    QAxObject*sheet=sheets->querySubObject("Item(int)",1);

    //获取单元格
    //保存表头
    QAxObject*cell;
    for(int i=0;i<header.count();i++)
    {
        cell=sheet->querySubObject("Cells(int,int)",1,i+1);
        cell->setProperty("Value",header.at(i));
    }

    //保存表内容
    for(int i=0;i<rows;i++)
    {
        for(int j=0;j<table->columnCount();j++)
        {
            QTableWidgetItem*item=table->item(i,j);
            cell=sheet->querySubObject("Cells(int,int)",i+2,j+1);
            cell->setProperty("Value",item->text());
        }
    }

    //保存工作簿
    workbook->dynamicCall("SaveAs(QString)",fileName);

    //关闭工作簿、结束进程
    workbook->dynamicCall("Close()");
    myExcell->dynamicCall("Quit()");

    QMessageBox::information(this,"信息","保存成功");
}

void Excel::OpenSQL()
{
    LinkSQL();
    QSqlQuery query;
    if(query.exec("select * from stu;")==false)
    {
        qDebug()<<db.lastError()<<endl;
        return;
    }

    int columnCount = query.record().count();
    QStringList headers;
    for (int i = 0; i < columnCount; ++i)
    {
        headers << query.record().fieldName(i);
    }
    ui->tableWidget->setColumnCount(columnCount);
    ui->tableWidget->setHorizontalHeaderLabels(headers);
    ui->tableWidget->setRowCount(0);

    int currentRow = 0;
    while (query.next())
    {
        QList<QTableWidgetItem*> items;
        for (int col = 0; col < columnCount; ++col)
        {
            items << new QTableWidgetItem(query.value(col).toString());
        }
        ui->tableWidget->insertRow(currentRow);
        for (int col = 0; col < columnCount; ++col)
        {
            ui->tableWidget->setItem(currentRow, col, items[col]);
        }
        currentRow++;
    }
}

void Excel::ClearSql()
{
    QSqlQuery query;
    query.exec("DELETE FROM stu");
    if (query.lastError().isValid()) {
        qDebug() << "清空数据出错: " << query.lastError().text();
    } else {
        qDebug() << "数据已成功清空";
    }
}

void Excel::ToSQL()
{
    LinkSQL();
    ClearSql();
    QSqlQuery query;
    // 获取表格行数和列数
    int rowCount = ui->tableWidget->rowCount();
    int columnCount = ui->tableWidget->columnCount();

    // 获取表格表头信息
    QStringList headerList;
    for (int col = 0; col < columnCount; ++col) {
        QTableWidgetItem *headerItem = ui->tableWidget->horizontalHeaderItem(col);
        if (headerItem) {
            headerList.append(headerItem->text());
        }
    }

    // 构建 SQL 插入语句
    QString insertSql = "insert into stu (";
    for (int col = 0; col < columnCount; ++col) {
        if (col > 0) {
            insertSql += ", ";
        }
        insertSql += headerList[col];
    }
    insertSql += ") values (";
    for (int col = 0; col < columnCount; ++col) {
        if (col > 0) {
            insertSql += ", ";
        }
        insertSql += ":value" + QString::number(col);
    }
    insertSql += ")";

    QSqlQuery insertQuery;
    insertQuery.prepare(insertSql);

    // 记录插入成功的行数
    int successCount = 0;

    // 遍历表格中的每一行
    for (int row = 0; row < rowCount; ++row) {
        // 设置插入语句的参数
        for (int col = 0; col < columnCount; ++col) {
            insertQuery.bindValue(":value" + QString::number(col), ui->tableWidget->item(row, col)->text());
        }

        // 执行插入语句
        if (insertQuery.exec()) {
            successCount++;
        } else {
            qDebug() << insertQuery.lastError() << endl;
        }
    }

    // 如果所有行都插入成功，则弹出插入成功对话框
    if (successCount == rowCount) {
        QMessageBox::information(this, "信息", "数据插入成功");
    }

    db.close();
}

